<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Read_tasks_model extends CI_Model
{
    public function __construct()
    {
        parent::__construct();
    }

    public function search_chg_list($zone,$task_name,$meter_reader){
        $this->db->query('DROP TABLE IF EXISTS infotable');
        $this->db->query('CREATE TEMPORARY TABLE infotable AS (SELECT urt.`task_id`, urt.task_name, urt.zone, urt.`month`, urt.`year`, urt.`seq_from`, urt.seq_to,CONCAT(au.`first_name`," ",au.`last_name`) name FROM ubs_read_tasks urt
    JOIN app_users au ON au.`user_id` = urt.`reader_id`)');

        $sql = "";
        $sql1 = ""; 
        $sql2 = "";
        $sql3 = "";
        $arr = array();
        if(!empty($zone)){
          $sql1 = " zone LIKE '". $zone ."' ";
          array_push($arr,$sql1);
        }
        if(!empty($task_name)){
          $sql2 = " task_name LIKE '". $task_name ."' ";
          array_push($arr,$sql2);
        }
        if(!empty($meter_reader)){
          $sql3 = " name LIKE '". $meter_reader ."' ";
          array_push($arr,$sql3);
        }

        $where = "WHERE ".implode(' AND ',$arr);

        $sql = "SELECT * FROM infotable ".$where;

        $q = $this->db->query($sql);
        return $q->result();
    }
    
    public function getzoning() {
      $query = $this->db->get('ar_zone1_values_v');
      return $query->result();
    }
    
    public function getusers(){
        return $this->db->query('SELECT au.user_id, CONCAT(au.first_name," ",au.last_name) name FROM app_users au,app_user_roles aur
    WHERE au.user_id = aur.user_id AND role_id = 19 AND au.user_id NOT IN (SELECT reader_id FROM ubs_read_tasks)' )->result();
    }

    public function getusers2(){
        return $this->db->query('SELECT au.user_id, CONCAT(au.first_name," ",au.last_name) name FROM app_users au,app_user_roles aur
    WHERE au.user_id = aur.user_id AND role_id = 19 ' )->result();
    }

    public function insert($data){
        $this->db->query('CALL ubs_read_tasks_ins(?,?,?,?,?,?,?,?,@err,@msg)',$data);
        $q = $this->db->query("SELECT @err as err, @msg as msg");
        return $q->result();
    }

    public function update($data){
        $this->db->query('CALL ubs_read_tasks_upd(?,?,?,?,?,?,?,?,?,@err,@msg)',$data);
        $q = $this->db->query("SELECT @err as err, @msg as msg");
        return $q->result();
    }

    public function getreadtasks($id){
        return $this->db->query('SELECT * FROM ubs_read_tasks WHERE task_id = "'.$id.'" ')->result();
    }
}